package com.basis.service;

import com.basis.utils.CheckSQLParamsUtil;
import com.basis.utils.HibernateDao;
import com.mes.data.EmesDatasource;
import com.mes.data.EmesDatasourceColumn;
import com.mes.data.EmesDatasourceParam;
import java.util.List;
import java.util.Map;

public class DataSourceService {
	public void parseSqlColumnAndParams(EmesDatasource ds) {
		CheckSQLParamsUtil util = CheckSQLParamsUtil.getInstance();
		String sql = util.getExeSQL(ds.getSql(), "1=0");
		List<Map<String, Object>> list = HibernateDao.getFields(sql);
		checkColumn(ds, list);
	}

	/**
	 * 检出字段
	 */
	private void checkColumn(EmesDatasource ds, List<Map<String, Object>> columnList) {
		Map<String, Object> column = null;

		long seq = 10;
		String columnNames = "";
		EmesDatasourceColumn sourceColumn = null;

		for (Map<String, Object> map : columnList) {
			column = this.getFieldText((String)map.get("name"));
			if (column != null && column.get("name") != null && !"".equals(column.get("name"))) {
				map.put("text", column.get("name"));
			}
			column = this.getFieldByName((String)map.get("name"), ds.getId());
			if (column == null) {
				sourceColumn = new EmesDatasourceColumn();
				sourceColumn.setColumnName((String)map.get("name"));
				sourceColumn.setName(map.get("text") == null ? (String)map.get("name") : (String)map.get("text"));
				sourceColumn.setSeq(seq);
				sourceColumn.setDataType(getColumnType((String)map.get("type"), map.get("fixed")));

				ds.getEmesDatasourceColumnList().add(sourceColumn);
			}
			columnNames += ",'" + (String)map.get("name") + "'";
			seq += 10;
		}
		clearColumn(columnNames.substring(1), ds.getId());
		this.checkOrSaveParams(ds, ds.getSql());
	}

	/**
	 * 检出SQL语句中的查询条件
	 */
	public void checkOrSaveParams(EmesDatasource ds, String pageSql) {
		Map<String, List<String>> params = CheckSQLParamsUtil.getInstance().checkParams(pageSql);
		String paramNames = "";

		EmesDatasourceParam sourceParam = null;

		int seq = 10;
		for (String code : params.keySet()) {
			if (getParamByName(code, ds.getId()) == null) {
				sourceParam = new EmesDatasourceParam();
				sourceParam.setParamName(code);
				sourceParam.setName(code);
				ds.getEmesDatasourceParamList().add(sourceParam);
			}
			paramNames += ",'" + code + "'";
			seq += 10;
		}
		clearParameter(paramNames.length() > 0 ? paramNames.substring(1) : "", ds.getId());
	}
	/**
	 * 清除无效列
	 */
	private void clearColumn(String columnNames, String id) {
		String sql = "delete Emes_Datasource_Column where Emes_Datasource_Id = ? and Column_Name not in (" + columnNames + ")";
		HibernateDao.executeSql(sql, id);
	}
	/**
	 * 清除无效查询条件
	 */
	private void clearParameter(String paramNames, String id) {
		String sql = "delete Emes_Datasource_Param where Emes_Datasource_Id = ?" + 
			(paramNames.length() > 0 ? "and DB_Parameter_Id not in (" + paramNames + ")" : "");
		HibernateDao.executeSql(sql, id);
	}
	/**
	 * 根据字段名获取表表列
	 * @param name
	 * @return
	 */
	private Map<String, Object> getFieldText(String name) {
		String sql = "select * from Emes_Datasource_Column where UPPER(Column_name) = ?";
		return HibernateDao.queryUnique(sql, name.toUpperCase());
	}
	
	private Map<String, Object> getFieldByName(String name, String dataSourceId) {
		String sql = "select * from Emes_Datasource_Column where UPPER(Column_name) = ? and Emes_Datasource_Id = ?";
		return HibernateDao.queryUnique(sql, name.toUpperCase(), dataSourceId);
	}
	
	private Map<String, Object> getParamByName(String paramName, String dataSourceId) {
		String sql = "select * from Emes_Datasource_Param where upper(param_name) = ? and Emes_Datasource_Id = ?";
		return HibernateDao.queryUnique(sql, paramName.toUpperCase(), dataSourceId);
	}
	/**
	 * 根据数据中型号生成类型
	 */
	private String getColumnType(String type, Object fixed) {
		if ("INT".equals(type.toUpperCase()) || "LONG".equals(type.toUpperCase())) {
			return "INT";
		}
		if ("FLOAT".equals(type.toUpperCase()) || "DOUBLE".equals(type.toUpperCase())) {
			return "FLOAT";
		}
		if ("NUMBER".equals(type.toUpperCase())) {
			if (fixed != null && Integer.parseInt(fixed.toString()) > 0) {
				return "FLOAT";
			}
			return "INT";
		}
		if ("DATE".equals(type.toUpperCase()) || "DATETIME".equals(type.toUpperCase())) {
			return "DATE";
		}
		return "STRING";
	}
}